package com.etool.modules.curd.service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import com.etool.modules.curd.model.Column;
import com.sun.org.apache.bcel.internal.generic.ARRAYLENGTH;

/**
 * @author linjia
 *
 */
@Component("curdService")
public class CurdService {

	@Resource
	private NamedParameterJdbcTemplate jdbc;
	
	
	private NamedParameterJdbcTemplate secondJdbc;

	/**
	 * 检查表是否存在
	 * 
	 * @param tableName
	 * @return
	 */
	public boolean tableExist(String tableName) {
		boolean result = false;

		String sql = "SHOW TABLES LIKE :tableName";
		List<Map<String, Object>> list = jdbc.queryForList(sql,
				Collections.singletonMap("tableName", tableName));

		if (list.size() == 1) {
			result = true;
		}
		return result;
	}

	/**
	 * 获取表结构
	 * 
	 * @param tableName
	 * @return
	 */
	public List<Column> desc(String tableName) {
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("tableName",this.getName(tableName));
		String sql = "select COLUMN_NAME,DATA_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA from information_schema.columns where table_name=:tableName";
		if(tableName.contains("."))
		{
			sql+=" and TABLE_SCHEMA=:tableSchema";
			params.put("tableSchema",this.getSchema(tableName));
		}
		
		List<Map<String, Object>> list = jdbc.queryForList(sql,params);

		List<Column> cols = new ArrayList<Column>();

		for (Map<String, Object> item : list) {
			Column col = new Column();
			col.setName((String) item.get("COLUMN_NAME"));
			col.setType(changeColumnTypeForMysql((String) item.get("DATA_TYPE")));
			col.setComment((String) item.get("COLUMN_COMMENT"));
			if(StringUtils.equals("PRI", (String) item.get("COLUMN_KEY")))
			{
				col.setKey(true);
			}
			if(StringUtils.equals("auto_increment", (String) item.get("EXTRA")))
			{
				col.setAuto_increment(true);
			}
			cols.add(col);
		}

		return cols;
	}
	
	
	public List<String> columns(String tableName) {
		List<Column> desc = desc(tableName);
		
		List<String> columns=new ArrayList<String>();
		
		for(Column item:desc)
		{
			columns.add(item.getName());
		}
		return columns;
		
	}
	
	private String getSchema(String tableName)
	{
		if(tableName.contains("."))
		{
			String[] array=tableName.split("\\.");
			if (array.length==2) {
				return array[0];
			}
		}
		return null;
	}
	
	private String getName(String tableName)
	{
		if(tableName.contains("."))
		{
			String[] array=tableName.split("\\.");
			if (array.length==2) {
				return array[1];
			}
		}
		else
		{
			return tableName;
		}
		return null;
	}

	/**
	 * 获取表内容
	 * @param tableName 表明
	 * @param params 查询条件
	 * @param offset 偏移量
	 * @param pageSize 页大小
	 * @return
	 */
	public List<Map<String, Object>> read(String tableName,
			Map<String, Object> params, int offset, int pageSize,String orderBy) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		List<Column> cols = desc(tableName);

		String colsStr = "";
		for (Column item : cols) {
			colsStr += item.getName() + ",";
		}

		sql.append(colsStr.substring(0, colsStr.length() - 1));
		sql.append(" from ");
		sql.append(tableName);
		sql.append(" where 1=1 ");

		if (params != null) {
			Set<String> entrys = params.keySet();

			for (String item : entrys) {
				sql.append(" and ");
				sql.append(item);
				sql.append("=:");
				sql.append(item);
			}
		}

		if(!StringUtils.isEmpty(orderBy))
		{
			sql.append(" order by ");
			sql.append(orderBy);
		}
		
		sql.append(" limit ");
		sql.append(offset);
		sql.append(",");
		sql.append(pageSize);
		
		

		List<Map<String, Object>> resutl = jdbc.queryForList(sql.toString(),
				params);
		return resutl;
	}
	
	public int count(String tableName,Map<String, Object> params)
	{
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(1) from ");
		sql.append(tableName);
		sql.append(" where 1=1 ");
		
		if (params != null) {
			Set<String> entrys = params.keySet();

			for (String item : entrys) {
				sql.append(" and ");
				sql.append(item);
				sql.append("=:");
				sql.append(item);
			}
		}
		
		int result=jdbc.queryForInt(sql.toString(), params);
		return result;
	}
	
	
	public List<Map<String, Object>> read(String tableName,
			Map<String, Object> params) {	
		return read(tableName, params,null);
	}
	
	public List<Map<String, Object>> read(String tableName,
			Map<String, Object> params,String orderBy) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		List<Column> cols = desc(tableName);

		String colsStr = "";
		for (Column item : cols) {
			colsStr += item.getName() + ",";
		}

		sql.append(colsStr.substring(0, colsStr.length() - 1));
		sql.append(" from ");
		sql.append(tableName);
		sql.append(" where 1=1 ");

		if (params != null) {
			Set<String> entrys = params.keySet();

			for (String item : entrys) {
				sql.append(" and ");
				sql.append(item);
				sql.append("=:");
				sql.append(item);
			}
		}
		
		if(!StringUtils.isEmpty(orderBy))
		{
			sql.append(" order by ");
			sql.append(orderBy);
		}
		
		List<Map<String, Object>> resutl = jdbc.queryForList(sql.toString(),
				params);
		return resutl;
	}
	
	/**
	 * 插入
	 * @param tableName 表明
	 * @param params 插入的字段
	 * @return
	 */
	public int create(String tableName,Map<String, Object> params)
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" replace into ");
		sql.append(tableName);
		sql.append(" ( ");
		
		List<Column> cols = desc(tableName);
		String colsStr = "";
		for (Column item : cols) {
			if(item.isAuto_increment() &&  StringUtils.isEmpty((String)params.get(item.getName())))
			{
				continue;
			}
			colsStr += item.getName() + ",";
		}

		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" ) values(");
		
		colsStr = "";
		for (Column item : cols) {
			if(item.isAuto_increment() &&  StringUtils.isEmpty((String)params.get(item.getName())))
			{
				continue;
			}
			colsStr += ":"+item.getName() + ",";
		}
		
		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" )");
		
		return jdbc.update(sql.toString(), params);
		
	}
	
	
	/**
	 * 延时插入
	 * @param tableName 表明
	 * @param params 插入的字段
	 * @return
	 */
	public int createDelayed(String tableName,Map<String, Object> params)
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT DELAYED into ");
		sql.append(tableName);
		sql.append(" ( ");
		
		List<Column> cols = desc(tableName);
		String colsStr = "";
		for (Column item : cols) {
			if(item.isAuto_increment() &&  StringUtils.isEmpty((String)params.get(item.getName())))
			{
				continue;
			}
			colsStr += item.getName() + ",";
		}

		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" ) values(");
		
		colsStr = "";
		for (Column item : cols) {
			if(item.isAuto_increment() &&  StringUtils.isEmpty((String)params.get(item.getName())))
			{
				continue;
			}
			colsStr += ":"+item.getName() + ",";
		}
		
		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" )");
		
		return jdbc.update(sql.toString(), params);
		
	}
	
	/**
	 * 更新
	 * @param tableName 表明
	 * @param params 更新的字段
	 * @return
	 */
	public int update(String tableName,Map<String, Object> params)
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" replace into ");
		sql.append(tableName);
		sql.append(" ( ");
		
		List<Column> cols = desc(tableName);
		String colsStr = "";
		for (Column item : cols) {
			colsStr += item.getName() + ",";
		}

		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" ) values(");
		
		colsStr = "";
		for (Column item : cols) {
			colsStr += ":"+item.getName() + ",";
		}
		
		sql.append(colsStr.substring(0, colsStr.length() - 1));
		
		sql.append(" )");
		
		return jdbc.update(sql.toString(), params);
		
	}
	
	/**
	 * 删除
	 * @param tableName 表明
	 * @param params 删除的条件
	 * @return
	 */
	public int delete(String tableName,Map<String, Object> params)
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from ");
		sql.append(tableName);
		sql.append(" where ");
		
		List<Column> cols = desc(tableName);
		String colsStr = "";
		for (Column item : cols) {
			if(params.get(item.getName())!=null)colsStr +=item.getName() + "=:"+item.getName()+" and ";
		}
		sql.append(colsStr.substring(0, colsStr.length() - 4));
		return jdbc.update(sql.toString(), params);
		
	}
	
	
	/**
	 * 执行一条sql
	 * @param sql
	 * @return
	 */
	public int updateSql(String sql)
	{
		return updateSql(sql,new HashMap<String, Object>());
	}
	
	public int updateSql(String sql,Map<String, Object> params)
	{
		return jdbc.update(sql,params);
	}


	
	/**
	 * 类型转换
	 * @param type
	 * @return
	 */
	private String changeColumnTypeForMysql(String type) {
		type = type.replaceAll("\\([0-9]*\\)", "");

		if (StringUtils.equalsIgnoreCase(type, "varchar")
				|| StringUtils.equalsIgnoreCase(type, "char")
				|| StringUtils.equalsIgnoreCase(type, "text")) {
			type = "string";
		}
		return type;
	}

	public void setSecondJdbc(NamedParameterJdbcTemplate secondJdbc) {
		this.secondJdbc = secondJdbc;
		NamedParameterJdbcTemplate tmpJdbc=this.jdbc;
		this.jdbc=secondJdbc;
		this.secondJdbc=tmpJdbc;
	}
	
	public void disableSecondJdbc() {
		if(secondJdbc!=null)
		{
			this.jdbc=this.secondJdbc;
			this.secondJdbc=null;
		}
	
	}
	
	
	
	

}
